﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

using Microsoft.Office.Interop.Excel;

namespace Lottery.WPSOffice {
    public static class ExcelGen {
        [DllImport("shell32.dll")]
        private static extern int ShellExecute(IntPtr hwnd, StringBuilder lpszOp, StringBuilder lpszFile, StringBuilder lpszParams, StringBuilder lpszDir, int FsShowCmd);

        /// <summary>
        /// 生产过滤表文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="qiShu">足彩期数</param>
        /// <param name="data"></param>
        public static void Run(string fileName, string qiShu, List<string> data) {
            // 创建ET
            var app = new ApplicationClass();
            app.Visible = false;
            app.UserName = " "; // 2009.04.27 老王提出生成的Excel文件中不要出现他的姓名

            //增加工作表
            app.Workbooks.Add(Type.Missing);
            //删除多余的两个Sheet
            app.ActiveWindow.SelectedSheets.Delete();
            app.ActiveWindow.SelectedSheets.Delete();
            app.WindowState = XlWindowState.xlMaximized;

            // 显示网格线
            app.ActiveWindow.DisplayGridlines = true;

            // 页边距
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;
            ws.PageSetup.LeftMargin = 0;
            ws.PageSetup.RightMargin = 0;
            ws.PageSetup.CenterHorizontally = true;

            ws.PageSetup.PrintTitleRows = "$1:$2";
            ws.PageSetup.RightFooter = "第 &P 页，共 &N 页";

            ws.Name = "过滤表";

            Range rm = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 18]);
            rm.Merge(true);
            ws.Cells[1, 1] = string.Format("胜负第 {0} 期：缩水过滤表", qiShu);

            ws.Cells[2, 1] = "序号";
            Range c = ws.Cells[2, 1] as Range;
            c.ColumnWidth = 5.25;
            for (int i = 1; i < 15; i++) {
                ws.Cells[2, 1 + i] = i.ToString();
                c = ws.Cells[2, 1 + i] as Range;
                c.ColumnWidth = 3.5;
            }

            ws.Cells[2, 16] = "限制范围";
            c = ws.Cells[2, 16] as Range;
            c.ColumnWidth = 9.38;

            ws.Cells[2, 17] = "正确";
            c = ws.Cells[2, 17] as Range;
            c.ColumnWidth = 3.5;

            ws.Cells[2, 18] = "限制名称";
            c = ws.Cells[2, 18] as Range;
            c.ColumnWidth = 12.75;

            c = ws.get_Range(ws.Cells[2, 1], ws.Cells[2, 18]);
            c.Interior.ColorIndex = 15; //颜色设置成灰色

            //写单元格的数据
            app.ErrorCheckingOptions.BackgroundChecking = false;
            const int lineCount = 18;
            int rowCount = data.Count / lineCount;

            // 设置网格线
            Range rs = null;
            if (data == null)
                rs = ws.get_Range(ws.Cells[2, 1], ws.Cells[2, 18]);
            else {
                rs = ws.get_Range(ws.Cells[2, 1], ws.Cells[2 + rowCount, 18]);
            }
            rs.Borders.LineStyle = XlLineStyle.xlContinuous;
            rs.Borders.Weight = XlBorderWeight.xlThin;
            rs.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rs.VerticalAlignment = XlVAlign.xlVAlignCenter;

            // 把单元格格式位置为文本
            rs.NumberFormatLocal = "@";

            if (data != null) {
                string[,] d = new string[rowCount, 18];
                for (int i = 0; i < rowCount; i++) {
                    for (int j = 0; j < 18; j++) {
                        d[i, j] = data[18 * i + j];
                    }
                }

                rs = ws.get_Range(ws.Cells[3, 1], ws.Cells[2 + rowCount, 18]);
                rs.Value2 = d;
            }

            for (int row = 3; row < rowCount + 3; row++) {
                var limit = (ws.Cells[row, 16] as Range).Text;
                if (string.IsNullOrEmpty(limit.ToString())) {
                    continue;
                }

                var cell = ws.Cells[row, 17] as Range;
                if (string.IsNullOrEmpty(cell.Text.ToString())) {
                    continue;
                }

                var lmt = limit.ToString().Split('-');
                try {
                    var low = Convert.ToInt32(lmt[0].Trim());
                    var up = Convert.ToInt32(lmt[1].Trim());
                    var cellData = Convert.ToInt32(cell.Text);
                    if (cellData < low || cellData > up) {
                        cell.Font.ColorIndex = 3;
                    }
                } catch {
                    continue;
                }
            }

            //保存文件
            app.DisplayAlerts = false;
            app.ActiveWorkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            ShellExecute(IntPtr.Zero, new StringBuilder("Open"), new StringBuilder(fileName), new StringBuilder(""), new StringBuilder(""), 1);
        }

        public static List<List<string>> Import(string fileName) {
            var result = new List<List<string>>();
            // 创建ET
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 3;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 18;
            if (rowOffset > 0) {
                var r = ws.get_Range(ws.Cells[3, 1], ws.Cells[2 + rowOffset, COL_COUNT]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        // 批量导入14场数据
        public static List<List<string>> Import2(string fileName) {
            var result = new List<List<string>>();
            // 创建ET
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 5;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 14 * 3;
            if (rowOffset > 0) {
                var r = ws.get_Range(ws.Cells[5, 2], ws.Cells[4 + rowOffset, COL_COUNT + 1]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        // 导入场次组合
        public static List<List<string>> Import3(string fileName) {
            var result = new List<List<string>>();
            // 创建ET
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 3;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 14;
            if (rowOffset > 0) {
                var r = ws.get_Range(ws.Cells[3, 2], ws.Cells[2 + rowOffset, COL_COUNT + 1]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        // 表3：从Excel中批量调入数据：调入预测文字
        public static List<List<string>> Import4(string fileName) {
            var result = new List<List<string>>();
            // 创建ET
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 6;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 14;
            if (rowOffset > 0) {
                var r = ws.get_Range(ws.Cells[6, 2], ws.Cells[5 + rowOffset, COL_COUNT + 1]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        // 表4：从Excel中批量调入数据：调入预测文字
        public static List<List<string>> Import5(string fileName) {
            var result = new List<List<string>>();
            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 6;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 14;
            if (rowOffset > 0) {
                var r = ws.get_Range(ws.Cells[6, 2], ws.Cells[5 + rowOffset, COL_COUNT + 1]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        /// <summary>
        /// 导入限制表数据, 表1用
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        /// <remarks>
        /// 返回结果为List[0]为第一列的数据，List[0][1]则为第一列第二行的单元格
        /// </remarks>
        public static List<List<string>> ImportLimitTable1(string fileName) {
            var result = new List<List<string>>();
            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            // 确定行
            var row = 2;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            // 确定列
            var col = 1;
            var colOffset = 0;
            while (true) {
                var cell = ws.Cells[2, col] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    colOffset++;
                }

                col++;
            }

            for (col = 2; col < 1 + colOffset; col++) {
                var list = new List<string>();
                result.Add(list);
                for (row = 2; row < 2 + rowOffset; row++) {
                    var cell = ws.Cells[row, col] as Range;
                    if (cell.Text != null)
                        list.Add(cell.Text.ToString());
                    else
                        list.Add(string.Empty);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        public static List<List<string>> ImportLimitTable2(string fileName) {
            // 表2的导入算法与表1一样
            return ImportLimitTable1(fileName);
        }

        /// <summary>
        /// 导入限制表数据, 表3用
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        /// <remarks>
        /// 返回结果为List[0]为第一列的数据，List[0][1]则为第一列第二行的单元格
        /// </remarks>
        public static List<List<string>> ImportLimitTable3(string fileName) {
            var result = new List<List<string>>();
            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            // 确定行
            var row = 5;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (cell.Text != null && string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            for (var col = 2; col < 17; col++) {
                var list = new List<string>();
                result.Add(list);
                for (row = 5; row < 5 + rowOffset; row++) {
                    var cell = ws.Cells[row, col] as Range;
                    if (cell.Text != null)
                        list.Add(cell.Text.ToString());
                    else
                        list.Add(string.Empty);
                }
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        /// <summary>
        /// 导入限制表数据, 表4用
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        /// <remarks>
        /// 返回结果为List[0]为第一列的数据，List[0][1]则为第一列第二行的单元格
        /// </remarks>
        public static List<List<string>> ImportLimitTable4(string fileName) {
            // 算法与导入表3一致
            return ImportLimitTable3(fileName);
        }
    }
}
